﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Entity;
using BLL;
using BLL.Access;
using System.Data;
using Platform.Http;
using Web.Class;
using System.IO;
using System.Data.Odbc;
using System.Data.OleDb;
using DevExpress.Web;

namespace Web.Pages.QuShui.DiXiaS._8.QiTaGL
{
    public partial class FaPiaoFHJK : BasePage
    {
        #region 属性
        /// <summary>
        /// 保存结果集1
        /// </summary>
        private DataTable dtData
        {
            get
            {
                return ViewState["dtData"] as DataTable;
            }
            set
            {
                ViewState["dtData"] = value;
            }
        }
        #endregion

        #region 变量
        SpHelper sphelper = new SpHelper();

        QS_QT_FAPIAOFHJKBLL fpbll = new QS_QT_FAPIAOFHJKBLL();
        #endregion

      

        #region 事件
        
       
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindLeft();
            }
            gvList.DataSource = this.dtData;
            gvList.DataBind();
        }
        protected void btnDaoRu_Click(object sender, EventArgs e)
        {
            if (Session["dt"] == null)
            {
                PageTools.AlertMsg(this, "请选择文件");
                return;
            }
            string filename = UpLoadFile.FileName;
            string res = "";
            //DataSet ds = sphelper.P_QS_QT_FAPIAOFHJKCX(3, "");
            DataTable newdt = Session["dt"] as DataTable;
            if (BusinessTool.InsertOracelData(newdt, "PLANWATER_TEMP.QS_TEMP_FAPIAOFHJK"))
            {
                DataTable dt = sphelper.P_QS_QT_FAPIAOFHJKDR(filename, 1, "", LoginInfo.CurrentUser.UserID.ToString()).Tables[0];
                res = dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "";
            }
            //sphelper.P_QS_QT_FAPIAOFHJKCX(3, "");
            Session["dt"] = null;
            HidList.Set("IsHave", "");
            //txtPath.Text = "";
            BindLeft();

        }
        protected void UpLoadFile_FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
        {
            string res = "-1";
            if (e.UploadedFile.IsValid)
            {
                //Session["dt"] = null;
                string fileName = e.UploadedFile.FileName;
                //string filepath1 = e.UploadedFile.

                string filePath = Path.Combine(Server.MapPath("/UPLOAD/QTGL"), fileName.Replace(Path.GetExtension(e.UploadedFile.FileName), "") + Path.GetExtension(e.UploadedFile.FileName));    //文件完整物理路径
                e.UploadedFile.SaveAs(filePath);
                DataTable ExcelTable = ReadExcel(filePath);
                if (ExcelTable.Rows.Count <= 0)
                {

                    e.CallbackData = string.Join(",", "您选择的文件里没有数据或数据不正确,请选择正确的文件", "", "2");
                    //"10|" + "您选择的文件里没有数据或数据不正确,请选择正确的文件";
                    return;
                }
                //Session["dt"] = ExcelTable;
                //return;
               
                //DataSet ds = sphelper.P_QS_QT_FAPIAOFHJKCX(3, "");
                //DataTable newdt = Session["dt"] as DataTable;
                if (BusinessTool.InsertOracelData(ExcelTable, "PLANWATER_TEMP.QS_TEMP_FAPIAOFHJK"))
                {
                    DataTable dt = sphelper.P_QS_QT_FAPIAOFHJKDR(fileName, 1, "", LoginInfo.CurrentUser.UserID.ToString()).Tables[0];
                    res = dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "-1";
                    e.CallbackData = string.Join(",", fileName, this.UpLoadFile.UploadedFiles[0].ContentLength, "1");
                }
                else
                {
                    e.CallbackData = "-1";
                }
                //sphelper.P_QS_QT_FAPIAOFHJKCX(3, "");
                //Session["dt"] = null;
                //HidList.Set("IsHave", "");
                //txtPath.Text = "";
                //BindLeft();
                 
            }
            else
            {
                e.CallbackData = "-1";
                return;
            }
        }
        
        
        #endregion

        #region 方法

        private void BindLeft()
        {
           // gvList.SettingsPager.Mode = GridViewPagerMode.EndlessPaging;
            this.dtData = sphelper.P_QS_QT_FAPIAOFHJKCX(1, "").Tables[0];
            gvList.DataSource = this.dtData;
            gvList.DataBind();
            if (this.dtData != null && this.dtData.Rows.Count > 0)
            {
                gvList.FocusedRowIndex = 0;
            }
            
        }
       

        protected void gvRight_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
        {
            string wjm = this.hdCollection.Get("WJM").ToString();
            DataTable dt = sphelper.P_QS_QT_FAPIAOFHJKCX(2, wjm).Tables[0];
            gvRight.DataSource = dt;
            gvRight.DataBind();
        }
        /// <summary>
        /// 前台表格行改变事件回调方法
        /// </summary>
        /// <param name="source"></param>
        /// <param name="e"></param>
        protected void ASPxCallback1_Callback(object source, DevExpress.Web.CallbackEventArgs e)
        {

            if (e.Parameter == "setlabel")
            {
                string msg = "0";
                string wjm = this.hdCollection.Get("WJM").ToString();
                DataTable dt = sphelper.P_QS_QT_FAPIAOFHJKCX(3, wjm).Tables[0];
                if (dt.Rows.Count>0)
                {
                    msg = dt.Rows[0][0].ToString();
                }
                e.Result = e.Parameter + "|0|" + msg;
            }
        }

        private DataTable ReadExcel(string ExcelFilePath)
        {
            DataTable dt = new DataTable();
            //string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" +ExcelFilePath + ";Extended Properties=Excel 8.0";
            //string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + ExcelFilePath;
            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + ExcelFilePath;
            OleDbConnection Connection = new OleDbConnection(strConnection);
            Connection.Open();
            string CommandText = "";
            DataTable dtsheetName = GetWorkBookName(Connection);
            if (dtsheetName != null && dtsheetName.Rows.Count > 0)
            {
                //I_Y	I_M	S_YONGHUH	S_YONGHUM	S_DIZHI	S_YOUBIAN	S_KAIHUZH		S_KAIHUYH	I_BENYUECM	I_BENYUESL	N_DANJIA	N_JINE	S_JINE	I_FEIYONGBH	S_JIAOFEIR							S_FAPIAOHM	S_POSTFILE	S_POSTDATE
                //              水量年	水量月	用户号	用户名	用户地址	邮编	银行帐号	开户户名	开户银行	本月抄码	开票数	单价	金额	大写金额	费用编号	缴款人	托收标志	费用类型	备注	联系人	财务户名	打印编号	收据NO	邮件号码	配送日期

                CommandText = @"select  水量年 as I_Y,水量月 as I_M,用户号 as S_YONGHUH,用户名 as S_YONGHUM,用户地址 as S_DIZHI,邮编 as S_YOUBIAN,银行帐号 as S_KAIHUZH,
                               开户银行 as S_KAIHUYH,本月抄码 as I_BENYUECM,开票数 as I_BENYUESL,单价 as N_DANJIA,金额 as N_JINE ,大写金额 as S_JINE,
                                费用编号 as I_FEIYONGBH,缴款人 as S_JIAOFEIR,[收据NO] as S_FAPIAOHM,邮件号码 as S_POSTFILE,配送日期 as S_POSTDATE,联系人 as S_DAYINR from 
                                [" + dtsheetName.Rows[0]["TABLE_NAME"].ToString() + "]";
            }
            else
            {

            }
            OleDbCommand comm = new OleDbCommand(CommandText, Connection);

            try
            {

                OleDbDataAdapter da = new OleDbDataAdapter(comm);
                DataSet Ds = new DataSet();
                da.Fill(Ds);
                dt = Ds.Tables[0];
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }

            return dt;

        }
        public static DataTable GetWorkBookName(OleDbConnection Connection)
        {
            DataTable Dt = null;
            try
            {
                if (Connection.State == ConnectionState.Closed) Connection.Open();
                Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }
            return Dt;
        }
        #endregion

        protected void gvList_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
        {
            BindLeft();
        }

       

       
    }
}